﻿'*******************************************************************
' Program: Data Center Visio Rack Tool
' Author: Albert E Edlund
' Date:
'
' Purpose:
'
'*******************************************************************

Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging

Imports Microsoft.Office.Interop.Visio

Imports System
Imports System.ComponentModel
Imports System.Collections.Generic
Imports System.Collections.ObjectModel

Imports System.Data
Imports System.IO
Imports System.Xml
Imports System.Collections
Imports System.Runtime.InteropServices

Imports visRackAppConnectivity.clsDC_RackData



Public Class clsAppConnections


#Region " Properties and declarations "

    Public m_db As Database
    Public m_ds As DataSet
    Public m_dt As DataTable

    Public m_dc As dc_AppConn
    Public m_oc As Collection(Of dc_AppConn)

    Private strUserId As String

    Public m_deleteCommand As DbCommand
    Public m_insertCommand As DbCommand
    Public m_updateCommand As DbCommand

    Public Const readAll As String = "selecttblAppConnection"


    Private m_clsVisio As visRackAppConnectivity.clsVisioSupport

    ' who am I
    Private myEnum As DC_RackTables = DC_RackTables.tblAppConnection

#End Region

#Region " Move/Transform Data "


    '
    ' create columns
    '
    Public Sub CreateTableColumns(ByRef tempTable As DataTable)

        Dim newDC As dc_AppConn = New dc_AppConn(strUserId)

        Try

            Dim colappconKey As New System.Data.DataColumn
            colappconKey.ColumnName = "appconKey"
            colappconKey.DataType = GetType(Int32)
            colappconKey.DefaultValue = newDC.appconKey
            tempTable.Columns.Add(colappconKey)

            '********************************************
            ' add columns
            '********************************************

            Dim colappconSolKey As New System.Data.DataColumn
            colappconSolKey.ColumnName = "appconSolKey"
            colappconSolKey.DataType = GetType(Int32)
            colappconSolKey.DefaultValue = newDC.appconSolKey
            tempTable.Columns.Add(colappconSolKey)

            Dim colappconAppAKey As New System.Data.DataColumn
            colappconAppAKey.ColumnName = "appconAppAKey"
            colappconAppAKey.DataType = GetType(Int32)
            colappconAppAKey.DefaultValue = newDC.appconAppAKey
            tempTable.Columns.Add(colappconAppAKey)

            Dim colappconAppBKey As New System.Data.DataColumn
            colappconAppBKey.ColumnName = "appconAppBKey"
            colappconAppBKey.DataType = GetType(Int32)
            colappconAppBKey.DefaultValue = newDC.appconAppBKey
            tempTable.Columns.Add(colappconAppBKey)

            Dim colappconComments As New System.Data.DataColumn
            colappconComments.ColumnName = "appconComments"
            colappconComments.DataType = GetType(String)
            colappconComments.DefaultValue = newDC.appconComments
            tempTable.Columns.Add(colappconComments)

            Dim colappconLastUpdate As New System.Data.DataColumn
            colappconLastUpdate.ColumnName = "appconLastUpdate"
            colappconLastUpdate.DataType = GetType(Date)
            colappconLastUpdate.DefaultValue = newDC.appconLastUpdate
            tempTable.Columns.Add(colappconLastUpdate)

            Dim colappconLastEdit As New System.Data.DataColumn
            colappconLastEdit.ColumnName = "appconLastEdit"
            colappconLastEdit.DataType = GetType(String)
            colappconLastEdit.DefaultValue = newDC.appconLastEdit
            tempTable.Columns.Add(colappconLastEdit)

            newDC = Nothing

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        If newDC IsNot Nothing Then
            newDC = Nothing
        End If

    End Sub


    '
    ' use for writing records
    '
    Public Sub movePropertiesToDataRow _
            (ByRef tempRow As DataRow, _
             ByVal tempDC As dc_AppConn)

        Try

            With tempDC
                tempRow.SetField("appconKey", .appconKey)
                '*******************************************************************************
                tempRow.SetField("appconSolKey", .appconSolKey)
                tempRow.SetField("appconAppAKey", .appconAppAKey)
                tempRow.SetField("appAppBKey", .appconAppBKey)

                tempRow.SetField("appconComments", .appconComments)
                tempRow.SetField("appconLastUpdate", .appconLastUpdate)
                tempRow.SetField("appconLastEdit", strUserId)
            End With



        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
            (ByVal tempRow As DataRow)

        Try

            With m_dc
                .initProperties()
                .appconKey = tempRow.Item("appconKey")
                '************************************************
                .appconSolKey = tempRow.Item("appconSolKey")
                .appconAppAKey = tempRow.Item("appAppAKey")
                .appconAppBKey = tempRow.Item("appconAppBKey")

                .appconComments = tempRow.Item("appconComments")
                .appconLastUpdate = tempRow.Item("appconLastUpdate")
                .appconLastEdit = tempRow.Item("appconLastEdit")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
            (ByVal tempRow As DataRow, _
             ByRef tempDC As dc_AppConn)

        Try

            With tempDC
                .initProperties()
                .appconKey = tempRow.Item("appconKey")
                '*******************************************************
                .appconSolKey = tempRow.Item("appconSolKey")
                .appconAppAKey = tempRow.Item("appAppAKey")
                .appconAppBKey = tempRow.Item("appconAppBKey")

                .appconComments = tempRow.Item("appconComments")
                .appconLastUpdate = tempRow.Item("appconLastUpdate")
                .appconLastEdit = tempRow.Item("appconLastEdit")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' for moving active table to collection for wpf
    '
    Public Sub moveDataTableToObservableCollection _
                (ByVal dtTemp As DataTable)

        Try

            If 0 < dtTemp.Rows.Count Then

                If m_oc IsNot Nothing Then
                    m_oc = Nothing
                    m_oc = New Collection(Of dc_AppConn)
                Else
                    m_oc = New Collection(Of dc_AppConn)
                End If

                Dim drTemp As DataRow
                Dim dcTemp As dc_AppConn
                For Each drTemp In dtTemp.Rows
                    dcTemp = New dc_AppConn
                    moveDataRowToProperties(drTemp, dcTemp)
                    m_oc.Add(dcTemp)
                Next

            End If

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub


    '
    '
    '
    Public Function moveDataTableToXML() As XmlDocument

        Dim writer = New System.IO.StringWriter
        Dim m_dt_xml As Xml.XmlDocument = Nothing

        Try

            m_dt.WriteXml(writer, XmlWriteMode.WriteSchema, True)
            m_dt_xml = Nothing
            m_dt_xml = New Xml.XmlDocument
            m_dt_xml.LoadXml(writer.ToString)

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally

            writer = Nothing

        End Try

        Return m_dt_xml

    End Function

#End Region

#Region " standard calls  "

    '
    '
    '
    Public Sub initDataSetAndTable()

        Try

            ' make sure the root stuff is there
            If m_ds Is Nothing Then
                m_ds = New DataSet
            Else
                m_ds = Nothing
                m_ds = New DataSet
            End If
            m_ds.SchemaSerializationMode = SchemaSerializationMode.IncludeSchema

            If m_dt Is Nothing Then
                m_dt = New DataTable
            Else
                m_dt = Nothing
                m_dt = New DataTable
            End If

            If m_oc Is Nothing Then
                m_oc = New Collection(Of dc_AppConn)
            Else
                m_oc = Nothing
                m_oc = New Collection(Of dc_AppConn)
            End If

            If m_dc Is Nothing Then
                m_dc = New dc_AppConn(strUserId)
            Else
                m_dc = Nothing
                m_dc = New dc_AppConn(strUserId)
            End If


            CreateTableColumns(m_dt)
            m_dt.TableName = DC_RackTables.tblAppConnection.ToString
            m_ds.Tables.Add(m_dt)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub


    '
    '
    '
    Private Sub buildUpdateAppConnectionCommand()

        Try
            m_updateCommand = m_db.GetStoredProcCommand("updateAppConnection")

            Dim cmdActive As DbCommand = m_updateCommand

            m_db.AddInParameter(cmdActive, "appconKey", DbType.Int32, "appconKey", DataRowVersion.Current)
            '**************************************************
            m_db.AddInParameter(cmdActive, "appconSolKey", DbType.Int32, "appconSolKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appconAppAKey", DbType.Int32, "appconAppAKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appconAppBKey", DbType.Int32, "appconAppBKey", DataRowVersion.Current)

            m_db.AddInParameter(cmdActive, "appconComments", DbType.String, "appconComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appconLastUpdate", DbType.Date, "appconLastUpdate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appconLastEdit", DbType.String, "appconLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Private Sub buildInsertAppConnectionCommand()

        Try
            m_insertCommand = m_db.GetStoredProcCommand("insertAppConnection")

            Dim cmdActive As DbCommand = m_insertCommand

            '**********************************************************
            m_db.AddInParameter(cmdActive, "appconSolKey", DbType.Int32, "appconSolKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appconAppAKey", DbType.Int32, "appconAppAKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appconAppBKey", DbType.Int32, "appconAppBKey", DataRowVersion.Current)

            m_db.AddInParameter(cmdActive, "appconComments", DbType.String, "appconComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appconLastUpdate", DbType.Date, "appconLastUpdate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appconLastEdit", DbType.String, "appconLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Private Sub buildDeleteAppConnectionCommand()

        Try
            m_deleteCommand = m_db.GetStoredProcCommand("deleteAppConnection")

            Dim cmdActive As DbCommand = m_deleteCommand
            m_db.AddInParameter(cmdActive, "appconKey", DbType.Int32, "appconKey", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' create a record based on property values move it to the generic datatable
    ' then copy the table to the database
    '
    Public Function addAppConnectionDataSourceRecord _
            (ByVal tempDC As dc_AppConn, _
             Optional ByVal blnSilent As Boolean = False) _
             As Integer

        Dim intRowsAffected As Integer = 0

        Try

            initDataSetAndTable()

            Dim m_dr As DataRow = m_ds.Tables(DC_RackTables.tblAppConnection.ToString).NewRow
            movePropertiesToDataRow(m_dr, tempDC)
            m_ds.Tables(DC_RackTables.tblAppConnection.ToString).Rows.Add(m_dr)
            intRowsAffected = dbUpdateDataSet()

            If blnSilent = False Then
                MsgBox("DataRowsUpated = " & intRowsAffected)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return intRowsAffected

    End Function

    '
    '
    '
    Public Function dbUpdateDataSet() As Int16

        Dim rowsAffected As Int16 = 0

        Try

            rowsAffected = m_db.UpdateDataSet(m_ds, DC_RackTables.tblAppConnection.ToString, _
                                        m_insertCommand, _
                                        m_updateCommand, _
                                        m_deleteCommand, _
                                        UpdateBehavior.Standard)
            'msgbox rowsAffected

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        Return rowsAffected

    End Function




#End Region

#Region " Database Calls "

    '
    '
    '
    Public Sub dbSelectAllAppConnectionData()


        Try


            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand(readAll)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAppConnection.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAppConnection.ToString)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Sub dbSelectAppConnBySolutionKey(ByVal intappconsolKey As Int32)


        Try

            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectAppConnBySolKey")
            m_db.AddInParameter(cmd, "@appconsolKey", DbType.Int32, intappconsolKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAppConnection.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAppConnection.ToString)

            If m_dt.Rows.Count = 1 Then
                Dim m_dr As System.Data.DataRow = m_dt.Rows(0)
                moveDataRowToProperties(m_dr)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try

    End Sub




#End Region

#Region " Constructor "

    Public Sub New()
        Try


            m_db = DatabaseFactory.CreateDatabase()

            strUserId = Environment.UserDomainName & "/" & Environment.UserName

            '
            ' init an object class so that the default constants are available
            ' to the table definitions
            '


            initDataSetAndTable()

            m_updateCommand = Nothing
            m_insertCommand = Nothing
            m_deleteCommand = Nothing

            buildInsertAppConnectionCommand()
            buildDeleteAppConnectionCommand()
            buildUpdateAppConnectionCommand()


            m_clsVisio = New visRackAppConnectivity.clsVisioSupport


        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try




    End Sub


#End Region

#Region " Visio Attributes "


    Public Sub addTestAppProperties()

        With m_dc
            .appconKey = -1
            .appconSolKey = -1
            .appconAppAKey = -1
            .appconAppBKey = -1
        End With

    End Sub




    Public Sub UpdateAppConnectionsProperties _
            (ByRef visShape As Microsoft.Office.Interop.Visio.Shape, _
             ByVal drApp As System.Data.DataRow)


        Try

            If Not (drApp Is Nothing) Then

                moveDataRowToProperties(drApp)

            Else
                addTestAppProperties()
            End If

            With m_dc
                m_clsVisio.ForceShapeData(visShape, "appconKey", .appconKey, "appCon")
                m_clsVisio.ForceShapeData(visShape, "appconSolKey", .appconSolKey, "appCon")
                m_clsVisio.ForceShapeData(visShape, "appconAppAKey", .appconAppAKey, "appCon")
                m_clsVisio.ForceShapeData(visShape, "appconAppBKey", .appconAppBKey, "appCon")
            End With


        Catch com As COMException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub



#End Region




End Class
